﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using eCitizen.DataContracts;

namespace eCitizen.DataAccess
{
    public class ReportLoggingService : dbService
    {
        
        public void LogEmailSendStatus(string agencyServiceCode,int totalSuccessful,int totalFailure)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;

            dbConnection.Open();

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_InsertEmailLog";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AgencyServiceCode";
            dbParameter.Value = agencyServiceCode;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ProcessedDateTime";
            dbParameter.Value = DateTime.Now;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@TotalSuccessful";
            dbParameter.Value = totalSuccessful;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@TotalFailure";
            dbParameter.Value = totalFailure;
            dbCommand.Parameters.Add(dbParameter);

            dbCommand.ExecuteNonQuery();

            dbConnection.Close();              
            
        }
    
        public void LogSmsSendStatus(SmsStatusReport statusReport)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);
            
            dbConnection.Open();


            foreach (SmsStatus statusRecord in statusReport.SmsStatusRecords)
            {

                SqlCommand dbCommand = new SqlCommand();
                SqlParameter dbParameter = null;
                dbCommand.Connection = dbConnection;
                dbCommand.CommandText = "eCitizen_Personalised.usp_InsertSmsLog";
                dbCommand.CommandType = CommandType.StoredProcedure;

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@AgencyServiceCode";
                dbParameter.Value = statusReport.ServiceId;
                dbCommand.Parameters.Add(dbParameter);

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@ProcessedDateTime";
                dbParameter.Value = statusRecord.ProcessedDateTime;
                dbCommand.Parameters.Add(dbParameter);

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@ProcessedFileName";
                dbParameter.Value = statusRecord.ProcessedFileName;
                dbCommand.Parameters.Add(dbParameter);

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@TotalSuccessful";
                dbParameter.Value = statusRecord.TotalSuccessful;
                dbCommand.Parameters.Add(dbParameter);

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@TotalPending";
                dbParameter.Value = statusRecord.TotalPending;
                dbCommand.Parameters.Add(dbParameter);

                dbParameter = new SqlParameter();
                dbParameter.ParameterName = "@TotalFailure";
                dbParameter.Value = statusRecord.TotalFailure;
                dbCommand.Parameters.Add(dbParameter);

                dbCommand.ExecuteNonQuery();
            }
            dbConnection.Close();     
        }

        public EmailStatusReport GetEMailStatusReport(EmailStatusReport emailReport)
        {
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);

            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            SqlParameter dbParameter = null;
            IDataReader dbReader = null;
            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_GetEmailStatusReportByDate";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ReportDate";
            dbParameter.Value = emailReport.ReportDate;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ServiceCode";
            dbParameter.Value = emailReport.ServiceId;
            dbCommand.Parameters.Add(dbParameter);


            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                EmailStatus statusRecord = new EmailStatus();

                statusRecord.ProcessedDateTime = dbReader.GetDateTime(dbReader.GetOrdinal("ProcessedDateTime"));
                statusRecord.TotalSuccessful = dbReader.GetInt32(dbReader.GetOrdinal("TotalSuccessful"));
                statusRecord.TotalFailure = dbReader.GetInt32(dbReader.GetOrdinal("TotalFailure"));

                emailReport.EmailStatusRecords.Add(statusRecord);
            }

            dbConnection.Close();

            return emailReport;
        }
   
    }
}
